Using OSQL with Microsoft SQL databases
OSQL basic commands
Logging into a database with osql
| c:\> | osql -S [servername\instance] -U username [-P password] |
Example1: Specifying the user and letting the command line prompt for a password.
c:\> osql -S Core\ldmsdata -U sa
password:
password:
Example2: Specifying the user and the password.
| c:\> | osql -S ld87\ldmsdata -U sa -P pw |
Example3: Using a trusted connection
| c:\> | osql -E |
Show databases
| 1> 2> |
select * from sysdatabases go |
Note: Or to see only the database Name row do the following:
| 1> 2> |
select Name from sysdatabases go |
Creating a database
| 1> 2> |
create database DatabaseName go |
Selecting a database
| 1> 2> |
USE master go |
Drop a database
| 1> 2> |
drop database DatabaseName go |
Show tables
| 1> 2> 1> 2> |
USE DatabaseName go select * from INFORMATION_SCHEMA.TABLES go |
Note: Or to see only the Table_Name row do the following:
| 1> 2> 1> 2> |
USE DatabaseName go select TABLE_NAME from INFORMATION_SCHEMA.TABLES go |
Drop a table
| 1> 2> 1> 2> |
USE DatabaseName go drop table TableName go |
Insert a row into a table
| 1> 2> 1> 2> |
USE DatabaseName go INSERT INTO TableName Values(“Column1value”,”Column2value”,”Column3value”) go |
Note: Or to insert by only providing values for a few columns and letting the other columns take the default values. This is useful when the first column is set to AUTO_INCREMENT.
| 1> 2> 1> 2> |
USE DatabaseName go INSERT INTO TableName (Col2, Col3) Values(“Column2value”,”Column3value”) go |
Update a value in row of a table
| 1> 2> 1> 2> |
USE DatabaseName go UPDATE TableName set ColumnName=’NewValue’ where SomeColumn=’whereValue’ go |
Drop a view
| 1> 2> 1> 2> |
USE DatabaseName go drop view ViewName go |
Backup a database
| 1> 2> |
BACKUP DATABASE ulddb TO DISK=’c:\path\to\dbbackup.bak’ WITH FORMAT go |
You can do this at the command prompt with one single command:
| c:\> | osql -S ld87\ldmsdata -U sa -P pw -Q “BACKUP DATABASE ulddb TO DISK=’c:\path\to\dbbackup.bak’ WITH FORMAT” |
Change the SA password with one line in a command prompt
| c:> | osql -E -S localhost\ldmsdata -d ULDDB -Q “sp_password NULL,’P@ssword’,’sa'” |
I hope this helps you.
More Information
For more information see the following website:
try fulltimefieldtrip.com
Rhyous » Blog Archive » Using OSQL with Microsoft SQL databases
raovatonline.org
Rhyous » Blog Archive » Using OSQL with Microsoft SQL databases
www.nhsdiscounts.org.uk
Rhyous » Blog Archive » Using OSQL with Microsoft SQL databases
Stwx explained in a blog post
Rhyous » Blog Archive » Using OSQL with Microsoft SQL databases
www.onecompare.com
Rhyous » Blog Archive » Using OSQL with Microsoft SQL databases
New post: Can you please share the command used for describing the table. I tried using "desc" command, but it is not working.
Pl. Help.
P.S.:posted new due to email ID types wrong
Can you please share the command used for describing the table. I tried using "desc" command, but it is not working.
Pl. Help.